﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.LevelDataAccessModel;
using GovernmentOfficeProject.SysTools;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_SYS_Menu
    {

        public Entity_SYS_Menu getMenuByMenuID(decimal menuID)
        {
            try
            {
                string strSQL = "select * from [SYS_MENU] where MenuID=@menuID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@menuID",menuID)
                };
                List<Entity_SYS_Menu> list = getMenuListBySql(strSQL,values);
                if (list.Count > 0) 
                {
                    return list[0];
                }
                return null;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.getMenuByMenuID(decimal menuID)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_SYS_Menu> getMenuList()
        {
            try
            {
                string strSQL = "select * from [SYS_MENU]";
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                List<Entity_SYS_Menu> menuList = getMenuListBySql(strSQL,null);
                return menuList;            
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.getMenuList()---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_SYS_Menu> getMenuListByMenuEntity(Entity_SYS_Menu menuEntity)
        {
            try
            {
                string strSQL = "select * from [SYS_MENU] where 1=1";
                if (!String.IsNullOrEmpty(menuEntity.MenuNo))
                {
                    strSQL += " and MenuNo='" + menuEntity.MenuNo + "'";
                }
                if (!String.IsNullOrEmpty(menuEntity.MenuParentNo))
                {
                    strSQL += " and MenuParentNo='" + menuEntity.MenuParentNo + "'";
                }
                if (menuEntity.MenuOrder != -1)
                {
                    strSQL += " and MenuOrder=" + menuEntity.MenuOrder;
                }
                if (!String.IsNullOrEmpty(menuEntity.MenuName))
                {
                    strSQL += " and MenuName='" + menuEntity.MenuName + "'";
                }
                if (!String.IsNullOrEmpty(menuEntity.MenuURL))
                {
                    strSQL += " and MenuUrl='" + menuEntity.MenuURL + "'";
                }
                if (!String.IsNullOrEmpty(menuEntity.MenuIcon))
                {
                    strSQL += " and MenuIcon='" + menuEntity.MenuIcon + "'";
                }
                if (menuEntity.MenuIsVisible != -1)
                {
                    strSQL += " and MenuIsVisible=" + menuEntity.MenuIsVisible;
                }
                if (menuEntity.MenuIsLeaf != -1)
                {
                    strSQL += " and MenuIsLeaf=" + menuEntity.MenuIsLeaf;
                }
                //AppLog.Write("DAL_getMenuListByMenuEntity(Entity_SYS_Menu menuEntity)的SQL语句:" + strSQL, AppLog.LogMessageType.Debug);
                List<Entity_SYS_Menu> menuList = getMenuListBySql(strSQL,new SqlParameter[]{});
                return menuList;        
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.getMenuListByMenuEntity(Entity_SYS_Menu menuEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool addMenu(Entity_SYS_Menu menuEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                StringBuilder sqlBudIns = new StringBuilder();
                StringBuilder sqlBudValue = new StringBuilder();
                sqlBudIns.Append("insert into [SYS_MENU](");
                sqlBudValue.Append(" values(");
                List<SqlParameter> sprList = new List<SqlParameter>();

                if (String.IsNullOrEmpty(menuEntity.MenuNo))
                {
                    sqlBudIns.Append("MenuNo,");
                    sqlBudValue.Append("@menuNo,");
                    sprList.Add(new SqlParameter("@menuNo", menuEntity.MenuNo));
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuParentNo))
                {
                    sqlBudIns.Append("MenuParentNo,");
                    sqlBudValue.Append("@menuParentNo,");
                    sprList.Add(new SqlParameter("@menuParentNo", menuEntity.MenuParentNo));
                    blState = true;
                }
                if (menuEntity.MenuOrder != -1)
                {
                    sqlBudIns.Append("MenuOrder,");
                    sqlBudValue.Append("@menuOrder,");
                    sprList.Add(new SqlParameter("@menuOrder", menuEntity.MenuOrder));
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuName))
                {
                    sqlBudIns.Append("MenuName,");
                    sqlBudValue.Append("@menuName,");
                    sprList.Add(new SqlParameter("@menuName", menuEntity.MenuName));
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuURL))
                {
                    sqlBudIns.Append("MenuUrl,");
                    sqlBudValue.Append("@menuUrl,");
                    sprList.Add(new SqlParameter("@menuUrl", menuEntity.MenuURL));
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuIcon))
                {
                    sqlBudIns.Append("MenuIcon,");
                    sqlBudValue.Append("@menuIcon,");
                    sprList.Add(new SqlParameter("@menuIcon", menuEntity.MenuIcon));
                    blState = true;
                }
                if (menuEntity.MenuIsVisible != -1)
                {
                    sqlBudIns.Append("MenuIsVisible,");
                    sqlBudValue.Append("@menuIsVisible,");
                    sprList.Add(new SqlParameter("@menuIsVisible", menuEntity.MenuIsVisible));
                    blState = true;
                }
                if (menuEntity.MenuIsLeaf != -1)
                {
                    sqlBudIns.Append("MenuIsLeaf,");
                    sqlBudValue.Append("@menuIsLeaf,");
                    sprList.Add(new SqlParameter("@menuIsLeaf", menuEntity.MenuIsLeaf));
                    blState = true;
                }

                if (blState)
                {
                    string strSQL = sqlBudIns.ToString().Substring(0, sqlBudIns.Length - 1) + ")" + sqlBudValue.ToString().Substring(0, sqlBudValue.Length - 1) + ")";
                    SqlParameter[] values = sprList.ToArray();
                    //AppLog.Write("DAL-增加Menu的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //添加用户失败:没有任何数据.
                    AppLog.Write("添加Menu失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.addMenu(Entity_SYS_Menu menuEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool updateMenuByMenuIDAndMenuEntity(decimal menuID,Entity_SYS_Menu menuEntity)
        {
            try
            {
                bool flag = false;                
                bool blState = false;
                List<SqlParameter> sprList = new List<SqlParameter>();
                string strSQL = "update [SYS_MENU] set";
                if (String.IsNullOrEmpty(menuEntity.MenuNo))
                {
                    strSQL += " MenuNo='" + menuEntity.MenuNo + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuParentNo))
                {
                    strSQL += " MenuParentNo='" + menuEntity.MenuParentNo + "',";
                    blState = true;
                }
                if (menuEntity.MenuOrder != -1)
                {
                    strSQL += " MenuOrder=" + menuEntity.MenuOrder + ",";
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuName))
                {
                    strSQL += " MenuName='" + menuEntity.MenuName + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuURL))
                {
                    strSQL += " MenuUrl='" + menuEntity.MenuURL + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(menuEntity.MenuIcon))
                {
                    strSQL += " MenuIcon='" + menuEntity.MenuIcon + "',";
                    blState = true;
                }
                if (menuEntity.MenuIsVisible != -1)
                {
                    strSQL += " MenuIsVisible=" + menuEntity.MenuIsVisible + ",";
                    blState = true;
                }
                if (menuEntity.MenuIsLeaf != -1)
                {
                    strSQL += " MenuIsLeaf=" + menuEntity.MenuIsLeaf + ",";
                    blState = true;
                }

                if (blState)
                {
                    strSQL = strSQL.Substring(0, strSQL.Length - 1) + " where MenuID=@menuID";
                    SqlParameter[] values = new SqlParameter[]{
                        new SqlParameter("@menuID",menuID)
                    };
                    AppLog.Write("DAL-修改Menu的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //修改按钮失败:没有需要修改的数据.
                    AppLog.Write("添加Menu失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.updateMenuByMenuIDAndMenuEntity(decimal menuID,Entity_SYS_Menu menuEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool deleteMenuByMenuID(decimal menuID)
        {
            try
            {
                bool flag = false;
                string strSQL = "delete from [SYS_MENU] where MenuID=@menuID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@menuID",menuID)
                };
                flag = DAL_DBGM.DeleteInfo(strSQL, values);
                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.addMenu(Entity_SYS_Menu menuEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }



        




        #region 用户权限相关

        /// <summary>
        /// 获得用户权限所有菜单
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<Entity_SYS_Menu> getMenuListByUser(decimal userId)
        {
            string sql = @" SELECT * FROM SYS_MENU M WHERE(MenuID IN({0}) OR MenuID IN({1})) AND MenuID NOT IN ({2}) ORDER BY  M.MenuIsLeaf,M.MenuOrder";

            //子查询1：获取用户有权限的菜单ID
            string query1 = @"SELECT DISTINCT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriMaster = 'PER_USER' AND P.PriMasterValue = {0} AND P.PriAccess = 'SYS_MENU' AND P.PriOperation = 'Permit'".FormatWith(userId);
            //子查询2：获取用户所属角色有权限的菜单ID
            string query2 = @"SELECT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriMaster = 'PER_ROLE' AND P.PriMasterValue IN  (SELECT M.URRoleID FROM PER_USER_ROLE M WHERE M.URUserID = {0}) AND P.PriAccess = 'SYS_MENU' AND P.PriOperation = 'Permit'".FormatWith(userId);
            //子查询3：获取用户禁止权限的菜单ID
            string query3 = @"SELECT DISTINCT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriMaster = 'PER_USER' AND P.PriMasterValue = {0} AND P.PriAccess = 'SYS_MENU' AND P.PriOperation = 'Forbid'".FormatWith(userId);

            sql = sql.FormatWith(query1, query2, query3);
            List<Entity_SYS_Menu> list = null;
            try
            {
                list = getMenuListBySql(sql,null);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Menu.getMenuListByUser()---", AppLog.LogMessageType.Error);
                throw ex;
            }
            return list;
        }

        #endregion




        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private List<Entity_SYS_Menu> getMenuListBySql(string sql,SqlParameter[] values)
        {
            if (values == null)
            {
                values = new SqlParameter[]{};
            }
            DataSet dst = DAL_DBGM.GetDataSet(sql,values);
            Entity_SYS_Menu menu = null;
            List<Entity_SYS_Menu> menuList = new List<Entity_SYS_Menu>();
            foreach (DataRow row in dst.Tables[0].Rows)
            {
                menu = new Entity_SYS_Menu();
                menu.MenuID = Convert.ToDecimal(row["MenuID"]);
                menu.MenuNo = Convert.ToString(row["MenuNo"]);
                menu.MenuParentNo = Convert.ToString(row["MenuParentNo"]);
                menu.MenuOrder = Convert.ToInt32(row["MenuOrder"]);
                menu.MenuName = Convert.ToString(row["MenuName"]);
                menu.MenuURL = Convert.ToString(row["MenuUrl"]);
                menu.MenuIcon = Convert.ToString(row["MenuIcon"]);
                menu.MenuIsVisible = Convert.ToInt32(row["MenuIsVisible"]);
                menu.MenuIsLeaf = Convert.ToInt32(row["MenuIsLeaf"]);
                menuList.Add(menu);
            }
            return menuList;
        }
    }
}
